﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using System.Xml;
using System.Xml.Linq;
using Windows.ApplicationModel.Core;
using Windows.Storage;
using Windows.Storage.Streams;
using Windows.UI;
using Windows.UI.Core;
using Windows.UI.Xaml;
using Windows.UI.Xaml.Media;
using com.IronOne.BoardPACWinAppBO.Agenda;
using com.IronOne.BoardPACWinAppBO.General;
using com.IronOne.BoardPACWinAppBO.Meeting;
using com.IronOne.BoardPACWinAppBO.Paper;
using com.IronOne.BoardPACWinAppDAO.Category;
using com.IronOne.BoardPACWinAppDAO.DbOperations;
using com.IronOne.BoardPACWinAppDAO.DB;
using com.IronOne.BoardPACWinAppDAO.Mapper;
using com.IronOne.BoardPACWinAppUtil.Enums;
using com.IronOne.BoardPACWinAppUtil.Security;
using com.IronOne.BoardPACWinAppUtil.Util;
using SQLite;
using Version = com.IronOne.BoardPACWinAppDAO.DB.Version;

namespace com.IronOne.BoardPACWinAppDAO.Agenda
{
    public class AgendaDAO : IAgendaDAO
    {
        private readonly ICategoryDAO _categoryDAO;
        private readonly IDbOperations _dbOperations;

        private readonly string[] _randomStrings =
        {
            "!H@[dN^tt2C$a4k%", "!b68<A]dN^qX(C$*", "+>%-dN)M?[Cqa6k9", "fyH#Ii>*]Lb$/4k!", "S/$uR1e@N^(%7m4",
            "{%jWq^Z/z9$XyE)", "<T[hu$+-(sy^A?!9",
            "aB)[d$^?%U2C6jh@", "}CH+de]r@2cq?7k/", "%)Bh^#t[hIY5@D!+", "{-&bA3dN^f>Y2C$%", ">-m(IC}he@L8t^h",
            "(]?Lu$h+A9N<s!X",
            "@Bl}A7z[d0Klm!*", "$bUd{dh^ik7AG!J", "+T$ha.r0!nd]u@W?", "Dv?-[^f}Za$6/@O", "[$%3dN^M#2Cq<rkV",
            "%G@NU{k)eP$a4k?"
        };

        public AgendaDAO()
        {
            _dbOperations = new DbOperations.DbOperations();
            _categoryDAO = new CategoryDAO();
        }

        public async Task<List<PaperModel>> GetPapersToDownload()
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var paperEntityList =
                    await
                        conn.Table<Paper>()
                            .Where(
                                p =>
                                    p.DownloadStatus != (int) PaperDownloadStatuses.Downloded && p.VersionId != 0 &&
                                    p.DocType != (byte) DocumentType.DiscussionItem)
                            .OrderByDescending(w => w.LastUpadatedDateTime)
                            .ToListAsync();
                return EntityToModelMapper.MapPaperEntityToPaperModel(paperEntityList);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<PaperModel>> GetAnnotationFilesToDownload()
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var annotEntityList =
                    await
                        conn.Table<AnnotationShareWithUser>()
                            .Where(p => p.IsFileDownloaded != (int) PaperDownloadStatuses.Downloded)
                            .OrderByDescending(w => w.UpdatedTime)
                            .ToListAsync();
                return EntityToModelMapper.MapAnnotationShareWithUserEntityToPaperModel(annotEntityList);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<PaperModel> GetPaperById(int paperId = 0)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var paperEntity = await conn.Table<Paper>().Where(p => p.PaperId == paperId).FirstOrDefaultAsync();
                return EntityToModelMapper.MapPaperEntityToPaperModel(paperEntity);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IBuffer> ReadPdfDocument(PaperModel paperModel, int currentMemberId = 0)
        {
            try
            {
                var documentsFolder = await Utility.GetUserPdfFolder();
                var pdfFile = await GetFileName(documentsFolder, paperModel, currentMemberId);
                if (pdfFile == null) return null;

                var openFile = GenerateKey(paperModel, String.Empty);
                var encryptedBuffer = await FileIO.ReadBufferAsync(pdfFile);

                await StorageFolder.GetFolderFromPathAsync(ApplicationData.Current.TemporaryFolder.Path);
                return CryptoService.DecryptFile(encryptedBuffer, openFile, paperModel.VersionInfo);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<MemberModel> GetMemberDetailsById(string memId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var id = Convert.ToInt32(memId);
                var member = await conn.Table<Member>().Where(x => x.MemberId == id).FirstOrDefaultAsync();
                return EntityToModelMapper.MapMemberToMemberModel(member);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<MemberModel>> GetParticipantsListForMeeting(int meetingId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var meeting = await conn.Table<DB.Meeting>().Where(m => m.MeetingId == meetingId).FirstOrDefaultAsync();
                var memberIndividualAttendance = meeting.MemberIndividualAttendance;
                var attendance = meeting.MemberAttendence;

                var memberModels = new List<MemberModel>();
                var attendanceArr = attendance.Split('#');
                var memAttendance = new Dictionary<int, int>();

                var attendanceArrDouble = new Dictionary<string, string>();
                for (var i = 0; i < attendanceArr.Length; i++)
                {
                    attendanceArrDouble.Add(attendanceArr[i], attendanceArr[++i]);
                }
                Dictionary<string, string> attendanceLogArrDouble = null;

                var log =
                    await
                        conn.Table<Log>()
                            .Where(
                                m =>
                                    m.Action.Equals(ApplicationConstants.LogActions.UpdateMeetingPresence) &&
                                    m.MeetingID == meetingId)
                            .FirstOrDefaultAsync();

                if (log != null && !String.IsNullOrWhiteSpace(log.Info))
                {
                    var attendanceLogArr = log.Info.Split('#');
                    attendanceLogArrDouble = new Dictionary<string, string>();
                    for (var i = 0; i < attendanceLogArr.Length; i++)
                    {
                        attendanceLogArrDouble.Add(attendanceLogArr[i], attendanceLogArr[++i]);
                    }

                    foreach (var logItem in attendanceLogArrDouble)
                    {
                        if (attendanceArrDouble.ContainsKey(logItem.Key))
                            attendanceArrDouble[logItem.Key] = logItem.Value;
                        else
                            attendanceArrDouble.Add(logItem.Key, logItem.Value);
                    }
                }
                else //If Log has no records read it from Meeting table's info column, it has only updated records
                {
                    if (!String.IsNullOrEmpty(meeting.Info))
                    {
                        var attendanceLogArr = meeting.Info.Split('#');
                        attendanceLogArrDouble = new Dictionary<string, string>();
                        for (var i = 0; i < attendanceLogArr.Length; i++)
                        {
                            attendanceLogArrDouble.Add(attendanceLogArr[i], attendanceLogArr[++i]);
                        }

                        foreach (var logItem in attendanceLogArrDouble)
                        {
                            if (attendanceArrDouble.ContainsKey(logItem.Key))
                                attendanceArrDouble[logItem.Key] = logItem.Value;
                            else
                                attendanceArrDouble.Add(logItem.Key, logItem.Value);
                        }
                    }
                }

                foreach (var item in attendanceArrDouble)
                {
                    var memId = Convert.ToInt32(item.Key);
                    var memPartStatus = Convert.ToInt32(item.Value);
                    memAttendance.Add(memId, memPartStatus);
                }

                var memIds = memAttendance.Keys.ToList();

                var members = await MemberManip(conn, memIds);

                foreach (var t in members)
                {
                    var mm = EntityToModelMapper.MapMemberToMemberModel(t);
                    mm.AttendanceStatus = memAttendance[mm.MemberId];

                    string state;

                    #region AttendanceStatus ToString

                    switch (mm.AttendanceStatus)
                    {
                        case 0:
                            state = BoardMemberParticipationStatus.Absent.ToString();
                            break;
                        case 1:
                            state = BoardMemberParticipationStatus.Present.ToString();
                            break;
                        case 2:
                            state = BoardMemberParticipationStatus.Excused.ToString();
                            break;
                        case 3:
                            state = BoardMemberParticipationStatus.ConCall.ToString();
                            break;
                        default:
                            state = BoardMemberParticipationStatus.Absent.ToString();
                            break;
                    }

                    #endregion

                    if (Global.IsBoardSecrartary)
                    {
                        //NOTE: -1 is very important. Don't change
                        int index;

                        #region Switch

                        var enumValue =
                            (BoardMemberParticipationStatus)
                                Utility.GetEnumValue(typeof (BoardMemberParticipationStatus), state);
                        switch (enumValue)
                        {
                            case BoardMemberParticipationStatus.Absent:
                                var sTat4 = BoardMemberParticipationStatus.Absent.ToString();
                                index = (int) BoardMemberParticipationStatus.Absent;
                                var brush4 = new SolidColorBrush(Colors.DarkGray);

                                mm.StatusText = sTat4;
                                mm.AttendanceStatus = index;
                                mm.StatusColorCode = brush4;
                                break;

                            case BoardMemberParticipationStatus.Present:
                                var sTat0 = BoardMemberParticipationStatus.Present.ToString();
                                index = (int) BoardMemberParticipationStatus.Present;
                                var brush0 = new SolidColorBrush(Colors.Green);

                                mm.StatusText = sTat0;
                                mm.AttendanceStatus = index;
                                mm.StatusColorCode = brush0;
                                break;

                            case BoardMemberParticipationStatus.ConCall:
                                var sTat2 = BoardMemberParticipationStatus.ConCall.ToString();
                                index = (int) BoardMemberParticipationStatus.ConCall;
                                var brush2 = new SolidColorBrush(Colors.DeepSkyBlue);

                                mm.StatusText = sTat2;
                                mm.AttendanceStatus = index;
                                mm.StatusColorCode = brush2;
                                break;

                            case BoardMemberParticipationStatus.Excused:
                                var sTat1 = BoardMemberParticipationStatus.Excused.ToString();
                                index = (int) BoardMemberParticipationStatus.Excused;
                                var brush1 = new SolidColorBrush(Colors.OrangeRed);

                                mm.StatusText = sTat1;
                                mm.AttendanceStatus = index;
                                mm.StatusColorCode = brush1;
                                break;
                        }

                        #endregion
                    }
                    else
                    {
                        mm.StatusColorCode = new SolidColorBrush(Colors.DarkGray);
                    }

                    if (mm.IsActive != (int) BoardMemberIsActive.Active)
                    {
                        mm.ListItemBgColor = "LightGray";
                        mm.StatusColorCode = new SolidColorBrush(Colors.Gray);
                    }

                    if (attendanceLogArrDouble != null && attendanceLogArrDouble.ContainsKey(mm.MemberId.ToString()) &&
                        Global.UserId != mm.MemberId)
                        mm.IsSyncInProgressRing = Visibility.Visible;

                    memberModels.Add(mm);
                }

                var logIndiv =
                    await
                        conn.Table<Log>()
                            .Where(
                                m =>
                                    m.Action.Equals(ApplicationConstants.LogActions.UpdateIndividualPresence) &&
                                    m.MemberId == Global.UserId && m.MeetingID == meetingId)
                            .FirstOrDefaultAsync();
                var finalModel = new List<MemberModel>();
                foreach (var id in memIds)
                {
                    var id1 = id;
                    var model = memberModels.Find(x => x.MemberId == id1);
                    if (model == null) continue;

                    if (!String.IsNullOrEmpty(memberIndividualAttendance))
                    {
                        if (logIndiv != null)
                        {
                            /*string spl1 = memberIndividualAttendance.Split(new string[] { Global.UserId + "#" }, StringSplitOptions.None)[0];
                                string spl2 = memberIndividualAttendance.Split(new string[] { Global.UserId + "#" }, StringSplitOptions.None)[1];

                                if(spl2.Contains('#'))
                                    spl2 = spl2.Split(new char[] { '#' }, 2)[1];
                                else
                                    spl2 = "";

                                string final = spl1 + spl2 + Global.UserId + "#" + logIndiv.Data;*/

                            var spla1 = memberIndividualAttendance.Split('#');
                            var index1 = Array.IndexOf(spla1, Global.UserId.ToString());
                            spla1[index1 + 1] = logIndiv.Data;
                            var final = spla1.Aggregate("", (current, item) => current + (item + "#"));
                            final = final.TrimEnd('#');

                            memberIndividualAttendance = final;
                        }

                        var indAtt =
                            memberIndividualAttendance.Split(new[] {model.MemberId.ToString()}, StringSplitOptions.None)
                                [1];
                        indAtt = indAtt.Split('#')[1];
                        var idAtt = int.Parse(indAtt);
                        var attname = Enum.GetName(typeof (BoardMemberIndividualParticipationStatus), idAtt);
                        model.IndividualParticipationStatus = attname;
                    }

                    finalModel.Add(model);
                }

                return finalModel;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<MemberModel>> GetParticipantsListForMeetingWithApprovalStatus(int paperId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var paper = await conn.Table<Paper>().Where(m => m.PaperId == paperId).FirstOrDefaultAsync();
                if (String.IsNullOrEmpty(paper.MemberApprovalsString)) return null;
                var memApprovalsString = paper.MemberApprovalsString.TrimEnd('#');

                var memApprovalsCommentsString = paper.MemberApprovalComments;

                var memberModels = new List<MemberModel>();
                var memApprovalsArr = memApprovalsString.Split('#');

                var memApprovals = new Dictionary<string, string>();
                for (var i = 0; i < memApprovalsArr.Length; i++)
                {
                    memApprovals.Add(memApprovalsArr[i], memApprovalsArr[++i]);
                }

                var memAndAttendanceList = new Dictionary<int, int>();
                foreach (var item in memApprovals)
                {
                    var memId = Convert.ToInt32(item.Key);
                    var memPartStatus = Convert.ToInt32(item.Value);
                    memAndAttendanceList.Add(memId, memPartStatus);
                }

                var memIds = memAndAttendanceList.Keys.ToList();

                var members = await MemberManip(conn, memIds);

                for (var i = 0; i < members.Count; i++) //each (Member item in members)
                {
                    var mm = EntityToModelMapper.MapMemberToMemberModel(members[i]);
                    mm.PaperApprovalStatus = memAndAttendanceList[mm.MemberId];

                    if (String.IsNullOrEmpty(memApprovalsCommentsString))
                        mm.IsPaperApprovalCommentAvilable = Visibility.Collapsed;
                    else
                    {
                        mm.IsPaperApprovalCommentAvilable = memApprovalsCommentsString.Contains(mm.MemberId.ToString())
                            ? Visibility.Visible
                            : Visibility.Collapsed;

                        if (mm.IsPaperApprovalCommentAvilable == Visibility.Visible)
                        {
                            memApprovalsCommentsString =
                                memApprovalsCommentsString.TrimEnd(ApplicationConstants.Seperator);
                            var data = memApprovalsCommentsString.Split(ApplicationConstants.Seperator);
                            var index1 = Array.FindIndex(data, row => row.Equals(mm.MemberId.ToString()));

                            mm.PaperApprovalComment = data[++index1];
                        }
                    }

                    #region PaperApprovalStatus ToString

                    var state = "";
                    switch (mm.PaperApprovalStatus)
                    {
                        case 0:
                            state = DecisionStatus.Pending.ToString();
                            mm.PaperApprovalStatusTextColor = "Gray";
                            break;
                        case 1:
                            state = DecisionStatus.Approve.ToString();
                            mm.PaperApprovalStatusTextColor = "Green";
                            break;
                        case 2:
                            state = DecisionStatus.Reject.ToString();
                            mm.PaperApprovalStatusTextColor = "OrangeRed";
                            break;
                        case 3:
                            state = DecisionStatus.Abstain.ToString();
                            mm.PaperApprovalStatusTextColor = "DeepSkyBlue";
                            break;
                        case 4:
                            state = DecisionStatus.Interest.ToString();
                            mm.PaperApprovalStatusTextColor = "Blue";
                            break;
                        case 5:
                            state = DecisionStatus.Rpt.ToString();
                            mm.PaperApprovalStatusTextColor = "Black";
                            break;
                    }

                    #endregion

                    //mm.ListItemBgColor = "Transparent";
                    if (mm.IsActive != (int) BoardMemberIsActive.Active)
                    {
                        mm.ListItemBgColor = "LightGray";
                        mm.StatusColorCode = new SolidColorBrush(Colors.Gray);
                    }

                    mm.PaperApprovalStatusDisplay = state;
                    memberModels.Add(mm);
                }

                var memberIds = memAndAttendanceList.Keys.ToList();
                return memberIds.Select(item1 => memberModels.Find(x => x.MemberId == item1)).ToList();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<AgendaItem>> GetAgenda(int meetingId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var agendaLevelAuthorizationSetting = false; //TODO: Read from settings table
                List<Heading> headingList;
                List<Paper> paperList;
                var headingIds = new List<int>();
                var paperIds = new List<int>();
                const string path1 = "ms-appx:///Assets/Images/MeetingsPage/";
                const string path2 = "ms-appx:///Assets/Images/AgendaPage/";
                var agendaItemNormalColor = new SolidColorBrush(Color.FromArgb(255, 23, 133, 186));

                var itemWidth = 0;
                await
                    CoreApplication.MainView.CoreWindow.Dispatcher.RunAsync(CoreDispatcherPriority.Normal,
                        () => { itemWidth = (int) (Window.Current.Bounds.Width*0.42); //0.45
                        });

                if (agendaLevelAuthorizationSetting)
                {
                    headingList =
                        await
                            conn.Table<Heading>()
                                .Where(
                                    h => h.MeetingId == meetingId && h.IsAllowed == ApplicationConstants.AGENDAISALLOWED)
                                .ToListAsync();
                    headingIds.AddRange(headingList.Select(h => h.HeadingID));
                    paperList =
                        await
                            conn.Table<Paper>()
                                .Where(
                                    p =>
                                        headingIds.Contains((int) p.HeadingID) && p.RefId == meetingId && p.RefType == 1 &&
                                        ApplicationConstants.LivePaperStatuses.Contains(p.PaperStatus) &&
                                        p.IsAllowed != ApplicationConstants.AGENDAISHIDDEN)
                                .ToListAsync();
                    paperIds.AddRange(paperList.Select(p => p.PaperId));
                }
                else
                {
                    headingList = await conn.Table<Heading>().Where(h => h.MeetingId == meetingId).ToListAsync();
                    headingIds.AddRange(headingList.Select(h => h.HeadingID));
                    paperList =
                        await conn.Table<Paper>().Where(p => p.RefId == meetingId && p.RefType == 1).ToListAsync();
                    paperIds.AddRange(paperList.Select(p => p.PaperId));
                }

                var agendaItemList = EntityToModelMapper.MapHeadingToAgendaItem(headingList);

                foreach (var item in agendaItemList)
                {
                    item.ItemWidth = itemWidth;
                    item.Name = item.Name.Replace("\t ", " ");
                    item.Name = item.Name.Replace("\t", " ");
                }
                agendaItemList.AddRange(
                    EntityToModelMapper.MapPaperToAgendaItem(
                        paperList.Where(
                            p =>
                                headingIds.Contains((int) p.HeadingID) &&
                                ApplicationConstants.LivePaperStatuses.Contains(p.PaperStatus)).ToList()));

                var commentList = await conn.Table<DB.Comments>().Where(c => paperIds.Contains(c.PaperId)).ToListAsync();
                var pending =
                    (await
                        conn.Table<Log>()
                            .Where(x => x.Action.Equals(ApplicationConstants.LogActions.UpdateViewedPaper))
                            .ToListAsync()).Select(y => y.PaperID);
                var enumerable = pending as IList<int?> ?? pending.ToList();

                foreach (var item in agendaItemList.Where(a => a.HeadorPaper == 1))
                {
                    item.Name = item.Name.Replace("\t ", " ");
                    item.Name = item.Name.Replace("\t", " ");
                    item.MeetingId = meetingId;

                    item.CommentsCount =
                        commentList.Count(c => c.PaperId == item.Id && c.Status.Equals(0) && c.MemberId != Global.UserId);
                    //Comments viewed = 1, not viewed = 0
                    item.IsCommentsAvailable = (item.CommentsCount > 0) ? Visibility.Visible : Visibility.Collapsed;

                    item.CommentsCountDisplay = item.CommentsCount.ToString();
                    if (item.CommentsCount > Utility.GetMaxCountDisplay())
                    {
                        item.CommentsCountDisplay = Utility.GetMaxCountDisplayOnUi();
                    }

                    var brush =
                        new SolidColorBrush(Color.FromArgb(255, (byte) item.CellColorR, (byte) item.CellColorG,
                            (byte) item.CellColorB));

                    item.AgendaItemNameColor = agendaItemNormalColor;
                    item.PaperItemBgColor = brush.Color.ToString();
                    if (brush.Color.ToString().Equals("#FFFFFFFF"))
                        item.PaperItemBgColor = "#FFF2FBFF";

                    item.ItemWidth = itemWidth;

                    switch (item.NotificationStatus)
                    {
                        case (int) PapernotificationStatus.New:
                            item.MeetingStatusIconSource = path1 + "Meetings_SI_New.png";
                            item.MeetingStatusSideIconBgColor = "Black";
                            break;
                        case (int) PapernotificationStatus.Amended:
                            item.MeetingStatusIconSource = path1 + "Meetings_SI_Amended.png";
                            item.MeetingStatusSideIconBgColor = "Red";
                            break;
                        case (int) PapernotificationStatus.Read:
                            //item.MeetingStatusIconSource = path1 + "Meetings_SI_Downloded.png";
                            //item.MeetingStatusSideIconBgColor = "Green";
                            break;
                    }

                    if (pending != null && enumerable.Contains(item.Id))
                    {
                        item.MeetingStatusIconSource = "";
                        item.MeetingStatusSideIconVisibility = Visibility.Collapsed;
                        item.MeetingStatusSideIconBgColor = "Transparent";
                    }

                    switch (item.DocType)
                    {
                        case (byte) DocumentType.BoardPaper:
                            item.DocTypeIconPath = path2 + "Agenda_IconA.png";
                            break;
                        case (byte) DocumentType.InformationPaper:
                            item.DocTypeIconPath = path2 + "Agenda_IconI.png";
                            break;
                        case (byte) DocumentType.DiscussionItem:
                            item.DocTypeIconPath = path2 + "Agenda_IconD.png";
                            break;
                        case (byte) DocumentType.SupportDocument:
                            item.DocTypeIconPath = path2 + "Agenda_IconS.png";
                            break;
                        case (byte) DocumentType.DiscussionPaper:
                            item.DocTypeIconPath = path2 + "Agenda_IconD.png";
                            break;
                        case (byte) DocumentType.AudioVideo:
                            item.DocTypeIconPath = path2 + "Agenda_IconV.png";
                            break;
                    }

                    if (!Global.IsBoardSecrartary)
                    {
                        switch (item.DecisionStatus)
                        {
                            case (byte) DecisionStatus.Pending:
                                item.MemberDecisionStatusDisplay = DecisionStatus.Pending.ToString();
                                item.MemberDecisionStatusToView = DecisionStatus.Pending.ToString();
                                break;
                            case (byte) DecisionStatus.Approve:
                                item.MemberDecisionStatusDisplay = DecisionStatus.Approve.ToString();
                                item.MemberDecisionStatusToView = DecisionStatus.Approve + "d";
                                break;
                            case (byte) DecisionStatus.Reject:
                                item.MemberDecisionStatusDisplay = DecisionStatus.Reject.ToString();
                                item.MemberDecisionStatusToView = DecisionStatus.Reject + "ed";
                                break;
                            case (byte) DecisionStatus.Abstain:
                                item.MemberDecisionStatusDisplay = DecisionStatus.Abstain.ToString();
                                item.MemberDecisionStatusToView = DecisionStatus.Abstain + "ed";
                                break;
                            case (byte) DecisionStatus.Interest:
                                item.MemberDecisionStatusDisplay = DecisionStatus.Interest.ToString();
                                item.MemberDecisionStatusToView = DecisionStatus.Interest + "ed";
                                break;
                            case (byte) DecisionStatus.Rpt:
                                item.MemberDecisionStatusDisplay = DecisionStatus.Rpt.ToString();
                                break;
                        }
                    }
                    else
                    {
                        switch (item.PaperStatus)
                        {
                            case (byte) PaperStatus.Approved:
                                item.MemberDecisionStatusDisplay = PaperStatus.Approved.ToString();
                                item.MemberDecisionStatusToView = PaperStatus.Approved.ToString();
                                break;
                            case (byte) PaperStatus.Rejected:
                                item.MemberDecisionStatusDisplay = PaperStatus.Rejected.ToString();
                                item.MemberDecisionStatusToView = PaperStatus.Rejected.ToString();
                                break;
                            case (byte) PaperStatus.CarriedForward:
                                item.MemberDecisionStatusDisplay = PaperStatus.CarriedForward.ToString();
                                item.MemberDecisionStatusToView = PaperStatus.CarriedForward.ToString();
                                break;
                            default:
                                item.MemberDecisionStatusDisplay = PaperStatus.Pending.ToString();
                                item.MemberDecisionStatusToView = PaperStatus.Pending.ToString();
                                break;
                        }
                    }

                    if (item.DocType != null && (byte) item.DocType != (byte) DocumentType.DiscussionItem)
                        switch (item.DownloadStatus)
                        {
                            case (int) PaperDownloadStatuses.Downloding:
                                item.DownloadStatusIconPath = path2 + "Agenda_DownloadStart.png";
                                break;
                            case (int) PaperDownloadStatuses.NotDownloded:
                                item.DownloadStatusIconPath = path2 + "Agenda_DownloadPause.png";
                                break;
                            case (int) PaperDownloadStatuses.DownloadError:
                                item.DownloadStatusIconPath = path2 + "Agenda_DownloadPause.png";
                                break;
                            case (int) PaperDownloadStatuses.Downloded:
                                item.DownloadStatusIconPath = "";
                                break;
                            default:
                                item.DownloadStatusIconPath = "";
                                break;
                        }

                    item.MemberDecisionStatusDisplay = "Approval Status: " + item.MemberDecisionStatusDisplay;
                    if (String.IsNullOrWhiteSpace(item.PaperRefNo))
                        item.PaperRefNoDisplay = "Ref. Not Applicable";
                    else
                        item.PaperRefNoDisplay = "Ref. " + item.PaperRefNo;

                    #region Annotated Version

                    var versionList = await GetPaperVersionsByPaperId(item.Id, item.VersionId);
                    string annotateString = "Annotated Version: ";
                    if (versionList == null || versionList.Count <= 1)
                    {
                        item.AnnotateVersion = annotateString + "1.0";
                        continue;
                    }

                    item.AnnotateVersion = annotateString + versionList.Count + ".0";
                    #endregion
                }
                return agendaItemList;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<ResponseDataModel> WriteIndividualPartcipStatusToLogTable(LogDataModel ldm)
        {
            var rdm = new ResponseDataModel();

            var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

            await conn.RunInTransactionAsync(connection =>
            {
                connection = _dbOperations.GetSyncConnection(DbConnectionType.UserDbConnetion);
                try
                {
                    var entity = ModelToEntityMapper.LogDataModelToEntity(ldm);
                    var log =
                        connection.Table<Log>()
                            .FirstOrDefault(
                                x =>
                                    x.Action.Equals(ldm.Action) && x.MemberId == Global.UserId &&
                                    x.MeetingID == ldm.MeetingId);
                    if (log == null)
                    {
                        var queryResult1 = connection.Insert(entity);
                        if (queryResult1 == 0) rdm.IsError = true;
                    }
                    else
                    {
                        log = ModelToEntityMapper.MapLogDataModelForUpdate(log, entity);
                        var queryResult2 = connection.Update(log);
                        if (queryResult2 == 0) rdm.IsError = true;
                    }

                    var meeting = connection.Table<DB.Meeting>().FirstOrDefault(x => x.MeetingId == ldm.MeetingId);

                    var spla1 = meeting.MemberIndividualAttendance.Split('#');
                    var index1 = Array.IndexOf(spla1, Global.UserId.ToString());
                    spla1[index1 + 1] = ldm.Data;
                    var final = spla1.Aggregate("", (current, item) => current + (item + "#"));
                    final = final.TrimEnd('#');

                    meeting.MemberIndividualAttendance = final;
                    rdm.Data = final;
                    connection.Update(log);

                    connection.Commit();
                }
                catch (Exception)
                {
                    connection.Rollback();
                    rdm.IsError = true;
                }
            });
            return rdm;
        }

        public async Task<ResponseDataModel> WriteMemberAttendanceToLogTable(LogDataModel ldm)
        {
            var rdm = new ResponseDataModel();
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var entity = ModelToEntityMapper.LogDataModelToEntity(ldm);

                //Write to Meeting table's info column as well
                var meeting =
                    await conn.Table<DB.Meeting>().Where(x => x.MeetingId == ldm.MeetingId).FirstOrDefaultAsync();
                meeting.Info = ldm.Info; //Only updated records comes in 'ldm.Info'.
                await conn.UpdateAsync(meeting);

                var log =
                    await
                        conn.Table<Log>()
                            .Where(
                                x =>
                                    x.Action.Equals(ldm.Action) && x.MemberId == Global.UserId &&
                                    x.MeetingID == ldm.MeetingId)
                            .FirstOrDefaultAsync();
                if (log == null)
                {
                    var queryResult1 = await conn.InsertAsync(entity);
                    if (queryResult1 == 0) rdm.IsError = true;
                    return rdm;
                }

                log = ModelToEntityMapper.MapLogDataModelForUpdate(log, entity);
                var queryResult2 = await conn.UpdateAsync(log);

                if (queryResult2 == 0) rdm.IsError = true;
            }
            catch (Exception)
            {
                rdm.IsError = true;
            }
            return rdm;
        }

        public async Task<LogDataModel> GetIndividualPartcipStatusFromLogTable(int meetingId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var log =
                    await
                        conn.Table<Log>()
                            .Where(
                                x =>
                                    x.Action.Equals(ApplicationConstants.LogActions.UpdateIndividualPresence) &&
                                    x.MemberId == Global.UserId && x.MeetingID == meetingId)
                            .FirstOrDefaultAsync();
                if (log != null) return EntityToModelMapper.MapMemberToMemberModel(log);
                var meetingEntity =
                    await conn.Table<DB.Meeting>().Where(m => m.MeetingId == meetingId).FirstOrDefaultAsync();
                log = new Log
                {
                    Data = meetingEntity.MemberIndividualAttendance,
                    MeetingID = meetingId
                };
                return EntityToModelMapper.MapMemberToMemberModel(log);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<String> GetAprovalOptions(int meetingOrCircular)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var conditionMeetingOrCircular = (meetingOrCircular == 0
                    ? "ApprovalButtonsStatusForMeeting"
                    : "ApprovalButtonsStatusForCircular");
                var approvalOptions =
                    await
                        conn.Table<Settings>()
                            .Where(s => s.SettingName.Equals(conditionMeetingOrCircular))
                            .FirstOrDefaultAsync();
                return approvalOptions.SettingValue;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// </summary>
        /// <param name="action"></param>
        /// <param name="comment"></param>
        /// <param name="paperId"></param>
        /// <returns></returns>
        public async Task<bool> AddPaperApprovals(string action, string comment, int paperId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var isRecordExists =
                    await
                        conn.Table<Log>()
                            .Where(
                                l =>
                                    l.Action.Equals(ApplicationConstants.LogActions.UpdatePaperApproval) &&
                                    l.PaperID.Equals(paperId))
                            .CountAsync() > 0;
                //already has record in log table
                if (isRecordExists)
                {
                    //TODO : Update query goes here
                    var logEntity =
                        await
                            conn.Table<Log>()
                                .Where(
                                    l =>
                                        l.Action.Equals(ApplicationConstants.LogActions.UpdatePaperApproval) &&
                                        l.PaperID.Equals(paperId))
                                .FirstOrDefaultAsync();
                    logEntity.Data = comment;
                    logEntity.Date = DateTime.Now;
                    logEntity.UpdateID = Guid.NewGuid().ToString();

                    var rslt = await conn.UpdateAsync(logEntity);
                }
                else
                {
                    var logEntity = new Log
                    {
                        MemberId = Global.UserId,
                        PaperID = paperId,
                        Action = action,
                        Data = comment,
                        Date = DateTime.Now,
                        UpdateID = Guid.NewGuid().ToString()
                    };

                    var rslt = await conn.InsertAsync(logEntity);
                }
                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// </summary>
        /// <param name="paperId"></param>
        /// <returns>approval status</returns>
        public async Task<Tuple<int, bool>> GetAgendaItemApproval(int paperId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var logEntity =
                    await
                        conn.Table<Log>()
                            .Where(
                                l =>
                                    l.Action.Equals(ApplicationConstants.LogActions.UpdatePaperApproval) &&
                                    l.PaperID.Equals(paperId))
                            .FirstOrDefaultAsync();
                int approvalStat;
                bool isFromLogTable;

                if (logEntity != null)
                {
                    approvalStat = int.Parse(logEntity.Data.Contains(ApplicationConstants.Seperator) ? logEntity.Data.Split(ApplicationConstants.Seperator)[0] : logEntity.Data);

                    isFromLogTable = true;
                }
                else
                {
                    var paperEntity =
                        await conn.Table<Paper>().Where(p => p.PaperId.Equals(paperId)).FirstOrDefaultAsync();
                    approvalStat = paperEntity.UserApprovalStatus;
                    isFromLogTable = false;
                }
                return new Tuple<int, bool>(approvalStat, isFromLogTable);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<NewsAndSharedDocModel> GetNewsAndSharedDocsList()
        {
            try
            {
                var model = new NewsAndSharedDocModel();
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                try
                {
                    var newsEntities =
                        await
                            conn.Table<Paper>()
                                .Where(p => p.DocType == (int) PaperTypes.NewsItem)
                                .OrderBy(q => q.AgendaOrder)
                                .ToListAsync();
                    model.NewsItems = EntityToModelMapper.MapPaperEntityToPaperModel(newsEntities);
                }
                catch (Exception)
                {
                    model.NewsItems = null;
                }

                try
                {
                    var sharedDocEntities =
                        await
                            conn.Table<Paper>()
                                .Where(p => p.DocType == (int) PaperTypes.SharedDoc)
                                .OrderBy(q => q.AgendaOrder)
                                .ToListAsync();
                    model.SharedDocs = EntityToModelMapper.MapPaperEntityToPaperModel(sharedDocEntities);
                }
                catch (Exception)
                {
                    model.SharedDocs = null;
                }

                model.NewComments =
                    await
                        conn.Table<DB.Comments>().Where(p => p.Status == 0 && p.MemberId != Global.UserId).CountAsync();

                int[] livePaperStatuses = {1, 2, 3, 5};
                model.NewPapers =
                    await
                        conn.Table<Paper>()
                            .Where(
                                p =>
                                    p.NotificationStatus == (int) PapernotificationStatus.New &&
                                    livePaperStatuses.Contains(p.PaperStatus))
                            .CountAsync();

                return model;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IBuffer> GetNewsSharedDocPdfDocument(PaperModel globalSelectdItem)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var entity =
                    await conn.Table<Paper>().Where(p => p.PaperId == globalSelectdItem.PaperId).FirstOrDefaultAsync();
                if (entity == null || entity.DownloadStatus != (int) PaperDownloadStatuses.Downloded)
                    return null;

                return await ReadPdfDocument(globalSelectdItem, Global.UserId);
            }
            catch (Exception)
            {
                return null;
            }
        }

        public async Task<IBuffer> GetPastPaperPdfDocument(PaperModel globalSelectdItem)
        {
            try
            {
                return await ReadPdfDocument(globalSelectdItem, Global.UserId);
            }
            catch (Exception)
            {
                return null;
            }
        }

        public async Task<bool> CheckFileExistOnPdfFolder(int paperId, int versionId)
        {
            try
            {
                var fileName = paperId + "_" + Global.UserId + "_" + versionId;
                var documentsFolder = await Utility.GetUserPdfFolder();
                await documentsFolder.GetFileAsync(fileName);
                return true;
            }
            catch (Exception)
            {
                //NOTE: Don't write error logs here, this is not an error.
                return false;
            }
        }

        public async Task<bool> CheckAnnotationFileExistOnReceivedFolder(int paperId, int versionId, int annotationShareId)
        {
            try
            {
                var fileName = paperId + "_" + Global.UserId + "_" + versionId + "_" + annotationShareId + ApplicationConstants.ANNOTATEEXT;
                var receivedFolder = await Utility.GetShareReceivedFolder();
                await receivedFolder.GetFileAsync(fileName);
                return true;
            }
            catch (Exception)
            {
                //NOTE: Don't write error logs here, this is not an error.
                return false;
            }
        }

        public async Task<bool> CheckRestoreAnnotationFileExistOnOriginalFolder(int paperId, int versionId)
        {
            try
            {
                var fileName = paperId + "_" + Global.UserId + "_" + versionId + ApplicationConstants.ANNOTATEEXT;
                var originalFolder = await Utility.GetShareOriginalFolder();
                await originalFolder.GetFileAsync(fileName);
                return true;
            }
            catch (Exception)
            {
                //NOTE: Don't write error logs here, this is not an error.
                return false;
            }
        }

        public async Task<bool> UpdatePaperPresentStatus(int meetingId, int paperId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                await conn.RunInTransactionAsync(connection =>
                {
                    connection = _dbOperations.GetSyncConnection(DbConnectionType.UserDbConnetion);
                    try
                    {
                        var logEntity = new Log
                        {
                            MemberId = Global.UserId,
                            MeetingID = meetingId,
                            PaperID = paperId,
                            Action = ApplicationConstants.LogActions.UpdatePaperPresentStatus,
                            Date = DateTime.Now,
                            UpdateID = Guid.NewGuid().ToString()
                        }; //Create log table record
                        //TODO: set relevant value
                        //logEntity.Data = meetingNote;
                        //logEntity.Info = ""; //TODO: set relevant value
                        //logEntity.Status = ""; //TODO: set relevant value
                        //logEntity.LastUpdatedDateTime = null;//TODO: set relevant value
                        //logEntity.LastUpdatedTimeStamp = null;//TODO: set relevant value
                        //logEntity.UpdateID = "1";//TODO : set relevant value
                        connection.Insert(logEntity);

                        var paperEntity = (connection.Table<Paper>().FirstOrDefault(p => p.PaperId.Equals(paperId)));

                        paperEntity.paperPresentedStatus = 1;
                        connection.Update(paperEntity);
                        connection.Commit();
                    }
                    catch (Exception)
                    {
                        connection.Rollback();
                        throw;
                    }
                });
                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<AgendaModel> GetSelectedCategoryAndSubCategory(int categoryId, int subCategoryId)
        {
            try
            {
                var am = new AgendaModel
                {
                    SelectedCategory = await _categoryDAO.GetCategoryByCategoryId(categoryId),
                    SelectedSubCategory = await _categoryDAO.GetSubCategoryBySubCategoryId(subCategoryId)
                };
                return am;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<bool> UpdateViewedPaper(int memberId, int paperId, int versionId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                await conn.RunInTransactionAsync(connection =>
                {
                    connection = _dbOperations.GetSyncConnection(DbConnectionType.UserDbConnetion);
                    try
                    {
                        var logEntity = new Log
                        {
                            MemberId = Global.UserId,
                            PaperID = paperId,
                            Action = ApplicationConstants.LogActions.UpdateViewedPaper,
                            Data = versionId.ToString(),
                            Date = DateTime.Now,
                            UpdateID = Guid.NewGuid().ToString()
                        };

                        connection.Insert(logEntity);

                        var paperEntity = (connection.Table<Paper>().FirstOrDefault(p => p.PaperId.Equals(paperId)));
                        paperEntity.NotificationStatus = (byte) PapernotificationStatus.Read;
                        connection.Update(paperEntity);

                        connection.Commit();
                    }
                    catch (Exception)
                    {
                        connection.Rollback();
                        throw;
                    }
                });
                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<ResponseDataModel> UploadAnnotation(List<MemberModel> selectedList,
            AgendaItem globalSelectdAgendaItem)
        {
            try
            {
                var originalFolder = await Utility.GetShareOriginalFolder();
                var sentFolder = await Utility.GetShareSentFolder();

                var annotationFileName = globalSelectdAgendaItem.Id + "_" + Global.UserId + "_" +
                                         globalSelectdAgendaItem.VersionId + ApplicationConstants.ANNOTATEEXT;
                var annotationFileNewName = Guid.NewGuid() + ApplicationConstants.ANNOTATEEXT;

                var anntotationFile = await originalFolder.GetFileAsync(annotationFileName);
                await anntotationFile.CopyAsync(sentFolder, annotationFileNewName);

                var members = selectedList.Aggregate("", (current, item) => current + (item.MemberId + ","));
                members = members.TrimEnd(',');


                var rdm = new ResponseDataModel();
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                await conn.RunInTransactionAsync(connection =>
                {
                    connection = _dbOperations.GetSyncConnection(DbConnectionType.UserDbConnetion);
                    try
                    {
                        var logEntity = new Log
                        {
                            MemberId = Global.UserId,
                            PaperID = globalSelectdAgendaItem.Id,
                            Action = ApplicationConstants.LogActions.UploadAnnotation,
                            Info = annotationFileNewName,
                            Status = globalSelectdAgendaItem.VersionId.ToString(),
                            Data = members,
                            Date = DateTime.Now,
                            UpdateID = Guid.NewGuid().ToString()
                        };
                        //File Name goes here
                        var queryResult1 = connection.Insert(logEntity);

                        var asbu = new AnnotationShareByUser
                        {
                            PaperId = globalSelectdAgendaItem.Id,
                            SharedTime = DateTime.Now,
                            SharedWith = members,
                            VersionId = globalSelectdAgendaItem.VersionId,
                            info2 = annotationFileNewName
                        };
                        var queryResult2 = connection.Insert(asbu);

                        connection.Commit();
                    }
                    catch (Exception)
                    {
                        connection.Rollback();
                        rdm.IsError = true;
                    }
                });
                return rdm;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<bool> SaveBookmarks(Dictionary<int, string> bookmarks, string fileName)
        {
            try
            {
                fileName += ApplicationConstants.BOOKMARKEXT;
                var documentsFolder = await Utility.GetUserPdfFolder();
                var file = await documentsFolder.CreateFileAsync(fileName, CreationCollisionOption.ReplaceExisting);

                if (bookmarks.Count <= 0)
                {
                    await file.DeleteAsync(StorageDeleteOption.PermanentDelete);
                    return true;
                }

                using (var writeStream = await file.OpenAsync(FileAccessMode.ReadWrite))
                {
                    var s = writeStream.AsStreamForWrite();
                    var settings = new XmlWriterSettings {Async = true};
                    using (var writer = XmlWriter.Create(s, settings))
                    {
                        writer.WriteStartDocument();
                        writer.WriteStartElement("Bookmarks");

                        foreach (var item in bookmarks)
                        {
                            writer.WriteStartElement("Bookmark");
                            writer.WriteElementString("Name", item.Value);
                            writer.WriteElementString("PageNo", item.Key.ToString());
                            writer.WriteEndElement();
                        }
                        writer.WriteEndElement();
                        writer.WriteEndDocument();

                        await writer.FlushAsync();
                    }
                }

                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Dictionary<int, string>> LoadBookmarks(string fileName)
        {
            try
            {
                var bookmarks = new Dictionary<int, string>();
                fileName += ApplicationConstants.BOOKMARKEXT;
                var documentsFolder = await Utility.GetUserPdfFolder();
                StorageFile file = null;
                try
                {
                    file = await documentsFolder.GetFileAsync(fileName);
                }
                catch (Exception)
                {
                }

                if (file == null) return bookmarks;

                var readStream = await file.OpenAsync(FileAccessMode.Read);
                var xmldoc = XDocument.Load(readStream.AsStreamForRead());

                var mainElement = xmldoc.Element("Bookmarks");
                var elements = mainElement.Elements("Bookmark");
                foreach (var item in elements)
                {
                    bookmarks.Add(Convert.ToInt32(item.Element("PageNo").Value), item.Element("Name").Value);
                }

                return bookmarks; //.OrderBy(x => x.Key).ToDictionary(x => x.Key, x => x.Value);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<StorageFile> GetEmailReadyPaper(string paperName)
        {
            try
            {
                var documentsFolder =
                    await
                        StorageFolder.GetFolderFromPathAsync(Path.Combine(ApplicationData.Current.TemporaryFolder.Path));
                return await documentsFolder.GetFileAsync(paperName);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<PaperModel>> GetPaperVersionsByPaperId(int paperId, int versionId)
        {
            try
            {
                var model = new List<PaperModel>();

                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);
                var data =
                    await conn.Table<Version>().Where(x => x.PaperId == paperId).OrderBy(y => y.VersionId).ToListAsync();
                if (data == null || data.Count <= 0) return null;

                var originalFolder = await Utility.GetShareOriginalFolder();
                var userId = Global.UserId.ToString();

                var inc = 0;
                foreach (var item in data)
                {
                    inc++;
                    var annotationFileName = item.PaperId + "_" + userId + "_" + item.VersionId +
                                             ApplicationConstants.ANNOTATEEXT;
                    var itm = await originalFolder.TryGetItemAsync(annotationFileName);
                    if (itm != null)
                    {
                        model.Add(new PaperModel
                        {
                            PaperId = item.PaperId,
                            VersionId = item.VersionId,
                            VersionInfo = inc,
                            DocCreatedDateTime = item.CreatedDate
                        });
                    }
                    else
                    {
                        if (versionId == item.VersionId) //original item
                        {
                            model.Insert(0,
                                new PaperModel
                                {
                                    PaperId = item.PaperId,
                                    VersionId = item.VersionId,
                                    VersionInfo = inc,
                                    DocCreatedDateTime = item.CreatedDate
                                });
                        }
                    }
                }

                if (model.Count == 0 && data.Count > 0)
                {
                    model.Add(new PaperModel
                    {
                        PaperId = data[0].PaperId,
                        VersionId = data[0].VersionId,
                        VersionInfo = inc,
                        DocCreatedDateTime = data[0].CreatedDate
                    });
                }

                return model;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public async Task<PaperModel> GetOriginalPaperVersionByPaperId(int paperId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);
                var ver =
                    await
                        conn.Table<Version>()
                            .Where(x => x.PaperId == paperId)
                            .OrderByDescending(y => y.VersionId)
                            .FirstOrDefaultAsync();

                return new PaperModel {PaperId = ver.PaperId, VersionId = ver.VersionId};
            }
            catch (Exception)
            {
                return null;
            }
        }

        public async Task<List<PaperModel>> GetPaperVersionSentByUser(int paperId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);
                var data =
                    await
                        conn.Table<AnnotationShareByUser>()
                            .Where(x => x.PaperId == paperId)
                            .OrderBy(y => y.VersionId)
                            .ToListAsync();
                if (data == null || data.Count <= 0) return null;

                var model = new List<PaperModel>();
                var inc = 0;
                var ver = 0;
                foreach (var item in data)
                {
                    if (item.VersionId != ver)
                    {
                        inc++;
                        ver = item.VersionId;
                    }

                    var memIdsStr = item.SharedWith.Split(',');
                    var memIds = memIdsStr.Select(itemx => Convert.ToInt32(itemx)).ToList();

                    var members = await conn.Table<Member>().Where(m => memIds.Contains(m.MemberId)).ToListAsync();
                    var memberNames = members.Aggregate("", (current, itemi) => current + (itemi.FullName + "\n"));

                    model.Add(new PaperModel
                    {
                        PaperId = item.PaperId,
                        VersionId = item.VersionId,
                        VersionInfo = inc,
                        DocCreatedDateTime = item.SharedTime,
                        DocName = item.info2,
                        AsSharedDateTimeDisplay =
                            ((DateTime)(item.SharedTime)).ToString(Utility.GetDateTimeFormat()),
                        AsVersionDisplay = "V " + inc + ".0",
                        AsMemberNamesForFlyOut = memberNames
                    });
                }

                return model;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public async Task<List<PaperModel>> GetPaperVersionReceivedByUser(int paperId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);
                var data =
                    await
                        conn.Table<AnnotationShareWithUser>()
                            .Where(x => x.PaperId == paperId)
                            .OrderBy(y => y.VersionId)
                            .ToListAsync();
                if (data == null || data.Count <= 0) return null;

                var model = new List<PaperModel>();
                var inc = 0;
                var ver = 0;
                foreach (var item in data)
                {
                    if (item.VersionId != ver)
                    {
                        inc++;
                        ver = item.VersionId;
                    }

                    var item1 = item;
                    var member = await conn.Table<Member>().Where(m => m.MemberId == item1.UserId).FirstOrDefaultAsync();

                    model.Add(new PaperModel
                    {
                        AsAnnotationShareId = item.AnnotationShareId,
                        PaperId = item.PaperId,
                        VersionId = item.VersionId,
                        VersionInfo = inc,
                        DocCreatedDateTime = item.SharedTime,
                        AsSharedDateTimeDisplay = item.SharedTime.ToString(Utility.GetDateTimeFormat()),
                        AsVersionDisplay = "V " + inc + ".0",
                        AsMemberNamesForFlyOut = "By: " + member.FullName
                    });
                }

                return model;
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        ///     returns the document folder of the user. If folder does not exists, creates one.
        /// </summary>
        /// <param name="currentMemberId"></param>
        /// <returns></returns>
        public async Task<StorageFolder> GetDocumentFolder(int currentMemberId = 0)
        {
            try
            {
                var localFolder = ApplicationData.Current.LocalFolder;
                var userFolder =
                    await
                        localFolder.CreateFolderAsync(currentMemberId.ToString(), CreationCollisionOption.OpenIfExists);
                return
                    await userFolder.CreateFolderAsync(ApplicationConstants.PDF, CreationCollisionOption.OpenIfExists);
            }
            catch (Exception)
            {
                throw;
            }
        }

        private async Task<StorageFile> GetFileName(StorageFolder documentsFolder, PaperModel paperModel,
            int currentMemberId = 0)
        {
            try
            {
                var fileName = paperModel.PaperId + "_" + currentMemberId + "_" + paperModel.VersionId;
                return await documentsFolder.GetFileAsync(fileName);
            }
            catch (Exception)
            {
                //This is fine as an error - No need to log here
                return null;
            }
        }

        private string GenerateKey(PaperModel paperModel, string sample)
        {
            try
            {
                var culture = new CultureInfo("en-US");
                byte ssSize;
                if (paperModel.VersionInfo != ApplicationConstants.VersionThreeSharedS && paperModel.VersionInfo > -1 &&
                    paperModel.VersionInfo < 19)
                {
                    var characterarray = _randomStrings[paperModel.VersionInfo].Reverse().ToArray();
                    var s = new string(characterarray);
                    sample = s;
                    ssSize = 32;
                }
                else
                {
                    sample = _randomStrings[0];
                    ssSize = 16;
                }

                return
                    (((DateTime) paperModel.DocCreatedDateTime).ToString("M/d/yyyy h:mm:ss tt", culture)
                        .Replace(" ", "_") + sample).Substring(0, ssSize);
            }
            catch (Exception)
            {
                throw;
            }
        }

        private async Task<List<Member>> MemberManip(SQLiteAsyncConnection conn, List<int> memIds)
        {
            var sett1 =
                await
                    conn.Table<Settings>().Where(x => x.SettingName.Equals("ShowDeactivateUsers")).FirstOrDefaultAsync();
            var sett2 =
                await conn.Table<Settings>().Where(x => x.SettingName.Equals("ShowDeletedUsers")).FirstOrDefaultAsync();
            var showDeactivateUsers = (sett1.SettingValue.Equals("1"));
            var showDeletedUsers = (sett2.SettingValue.Equals("1"));

            var members = new List<Member>();

            if (showDeactivateUsers && showDeletedUsers)
                members = await conn.Table<Member>().Where(m => memIds.Contains(m.MemberId)).ToListAsync();
            else
            {
                if ((!showDeactivateUsers && !showDeletedUsers))
                    members =
                        await
                            conn.Table<Member>()
                                .Where(
                                    m =>
                                        memIds.Contains(m.MemberId) &&
                                        m.isActive != (int) BoardMemberIsActive.Deactive &&
                                        m.isActive != (int) BoardMemberIsActive.Deleted)
                                .ToListAsync();
                else
                {
                    if (!showDeactivateUsers && showDeletedUsers)
                        members =
                            await
                                conn.Table<Member>()
                                    .Where(
                                        m =>
                                            memIds.Contains(m.MemberId) &&
                                            m.isActive != (int) BoardMemberIsActive.Deactive)
                                    .ToListAsync();

                    if (showDeactivateUsers && !showDeletedUsers)
                        members =
                            await
                                conn.Table<Member>()
                                    .Where(
                                        m =>
                                            memIds.Contains(m.MemberId) &&
                                            m.isActive != (int) BoardMemberIsActive.Deleted)
                                    .ToListAsync();
                }
            }
            return members;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="paperId"></param>
        /// <param name="versionId"></param>
        /// <param name="currentPage">-1 if you don't need to update</param>
        /// <param name="isBackupNeeded"></param>
        /// <returns></returns>
        public async Task<bool> UpdateIsAnnotBackupNeededOnVersionTable(int paperId, int versionId, int currentPage, bool isBackupNeeded)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);
                var data =
                    await conn.Table<Version>().Where(x => x.PaperId == paperId && x.VersionId == versionId).FirstOrDefaultAsync();
                if (data == null) return false;

                int stat = isBackupNeeded ? (int)AnnotBackup.Need : (int)AnnotBackup.NoNeed;
                data.IsAnnotBackupNeeded = stat;
                if (currentPage > 0)
                data.CurrentPage = currentPage;
                await conn.UpdateAsync(data);

                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// [0] - IsBackupOnOff
        /// [1] - Backup duration in days
        /// [2] - Formatted datetime of last backup
        /// [3] - Formatted datetime of next backup
        /// </summary>
        /// <returns></returns>
        public async Task<string[]> GetAnnotationBackupDates()
        {
            try
            {
                string[] values = new string[4];
                string datetimePart = Utility.GetDateTimeFormat();

                var conn2 = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);
                var backupStat = await conn2.Table<Settings>().Where(x => x.SettingName.Equals(ApplicationConstants.UserDbSettingNames.AnnotationBackup)).FirstOrDefaultAsync();
                values[0] = backupStat.SettingValue;

                var backupDuration = await conn2.Table<Settings>().Where(x => x.SettingName.Equals(ApplicationConstants.UserDbSettingNames.AutomaticAnnotationBackupDuration)).FirstOrDefaultAsync();
                values[1] = backupDuration.SettingValue;
                
                var conn1 = _dbOperations.GetAsyncConnection(DbConnectionType.GeneralDbConnection);
                var last = await conn1.Table<AppSettings>().Where(x => x.SettingName.Equals(ApplicationConstants.GeneralDbSettingNames.LastAnnotationBackup)).FirstOrDefaultAsync();
                if (last == null)
                {
                    AppSettings sett = new AppSettings()
                    {
                        SettingName = ApplicationConstants.GeneralDbSettingNames.LastAnnotationBackup,
                        SettingValue = ""
                    };
                    await conn1.InsertAsync(sett);
                    values[2] = "";
                }
                else
                {
                    values[2] = "";
                    if (!String.IsNullOrWhiteSpace(last.SettingValue))
                    {
                        DateTime dtx = Convert.ToDateTime(last.SettingValue);
                        values[2] = dtx.ToString(datetimePart);
                    }
                }

                if (String.IsNullOrWhiteSpace(values[2]))
                {
                    values[3] = "";
                    if (values[0].Equals("1")) //backup is on
                    {
                        DateTime dt = DateTime.Now;
                        int dur = Convert.ToInt32(backupDuration.SettingValue);
                        dt = dt.AddDays(dur);
                        values[3] = dt.ToString(datetimePart);
                    }
                }
                else
                {
                    DateTime dt = Convert.ToDateTime(values[2]);
                    int dur = Convert.ToInt32(backupDuration.SettingValue);
                    dt = dt.AddDays(dur);

                    values[3] = dt.ToString(datetimePart);
                }

                return values;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<bool> SetAnnotationBackupDates()
        {
            try
            {
                string dt = DateTime.Now.ToString(Utility.GetDateTimeFormat());
                var conn1 = _dbOperations.GetAsyncConnection(DbConnectionType.GeneralDbConnection);
                var last = await conn1.Table<AppSettings>().Where(x => x.SettingName.Equals(ApplicationConstants.GeneralDbSettingNames.LastAnnotationBackup)).FirstOrDefaultAsync();
                if (last == null)
                {
                    AppSettings sett = new AppSettings()
                    {
                        SettingName = ApplicationConstants.GeneralDbSettingNames.LastAnnotationBackup,
                        SettingValue = dt
                    };
                    await conn1.InsertAsync(sett);
                }
                else
                {
                    last.SettingValue = dt;
                    await conn1.UpdateAsync(last);
                }

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public async Task<int> GetCurrentPageOfThePaper(int paperId, int versionId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);
                var record = await conn.Table<Version>().Where(x => x.PaperId == paperId && x.VersionId == versionId).FirstOrDefaultAsync();
                return record.CurrentPage;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<PaperModel>> GetBackupNeededAnnotationsList()
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);
                var record = await conn.Table<Version>().Where(x => x.IsAnnotBackupNeeded == (int)AnnotBackup.Need).ToListAsync();

                if (record != null && record.Any())
                {
                    List<PaperModel> list = new List<PaperModel>();
                    foreach (var item in record)
                    {
                        list.Add(new PaperModel { PaperId= item.PaperId, VersionId = item.VersionId });
                    }

                    return list;
                }
            }
            catch (Exception)
            {
                throw;
            }
            return null;
        }

        /*public async Task<bool> UpdateRestoreAnnotationTable(int backupId, PaperDownloadStatuses downloadStatus)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);
                var record = await conn.Table<AnnotationRestore>().Where(x => x.BackupID == backupId).FirstOrDefaultAsync();
                record.DownloadStatus = (int)downloadStatus;
                await conn.UpdateAsync(record);

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }*/


        public async Task<int> GetPendingApprovalsPapersList(int days)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);
                
                var pendingApprovalsCount =
                        await
                            conn.ExecuteScalarAsync<int>(
                                "SELECT COUNT(*) AS Count FROM Paper " +
                        "WHERE julianday('now') - julianday(Paper.DocCreatedDateTime) > " + days + " AND Paper.UserApprovalStatus = " + (int)PaperStatus.Approved);
               
                return pendingApprovalsCount;
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}